package com.jsmtr.rms.service.impl;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.springframework.stereotype.Service;

import com.jsmtr.rms.dao.DaoService;
import com.jsmtr.rms.entity.DUserResume;
import com.jsmtr.rms.entity.HRZZInterview;
import com.jsmtr.rms.entity.HRZZUser;
import com.jsmtr.rms.entity.ProjectInfo;
import com.jsmtr.rms.entity.ProjectInfoFlow;
import com.jsmtr.rms.entity.UserResume;
import com.jsmtr.rms.service.IDoZZService;
import com.jsmtr.rms.utils.BeanTools;
import com.jsmtr.rms.utils.StringTools;
import com.jsmtr.rms.utils.db.DbUtil;

@Service
public class DoZZService implements IDoZZService {

    @Resource
    private DaoService dao;

    public void setDao(DaoService dao) {
        this.dao = dao;
    }

    @Override
    public HRZZUser getUser(String id) throws Exception {
        return (HRZZUser) dao.findFirst("from HRZZUser where id='" + id + "'");
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<HRZZUser> getUsers(String[] ids) throws Exception {
        return dao.find("from HRZZUser where id in(:idlist)", ids);
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<DUserResume> getUsersDuser(String[] ids) throws Exception {
        return dao.find("from DUserResume where userid in(:idlist)", ids);
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<Object[]> getUsers(String pid) throws Exception {
        return dao.find("select d.name,d.cardno,d.nowaddress from DUserResume d,HRZZUser u where d.id=u.id and u.pid='" + pid + "' and u.fqstate='未放弃'");
    }

    @Override
    public ProjectInfoFlow getCurrentFlow(String fid) throws Exception {
        return (ProjectInfoFlow) dao.findFirst("from ProjectInfoFlow where id='" + fid + "'");
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<Object[]> getAllDeptInProject(String pid) throws Exception {
        String sql = "select distinct(deptcode),deptname from HR_ZZUSER where pid='" + pid + "'";
        return dao.executeSQLQuery(sql);
    }

    @Override
    public int getUserCount(String pid, String deptcode, int state) {
        try {
            String hql = "select count(*) from HRZZUser where pid='" + pid
                    + "' and deptcode='" + deptcode + "' and state='" + state + "'";
            return ((Long) dao.findUnique(hql)).intValue();
        } catch (Exception e) {
            e.printStackTrace();
            return -1;
        }
    }

    @Override
    public ProjectInfoFlow getflow(String pid, String deptcode) throws Exception {
        return (ProjectInfoFlow) dao.findFirst("from ProjectInfoFlow where pid='" + pid + "' and deptcode='" + deptcode + "'");
    }

    @Override
    public ProjectInfoFlow getCurrentFlow(String pid, String deptcode, String fid) throws Exception {
        return (ProjectInfoFlow) dao.findUnique("from ProjectInfoFlow where pid='" + pid + "' and deptcode='" + deptcode + "' and id='" + fid + "'");
    }

    @Override
    public ProjectInfoFlow getNextFlow(String pid, String deptcode, String fid) throws Exception {
        StringBuilder hql = new StringBuilder("from ProjectInfoFlow where pid='" + pid + "' and deptcode='" + deptcode + "' ");
        hql.append("and forder>(select forder from ProjectInfoFlow where id='" + fid + "') order by forder");
        return (ProjectInfoFlow) dao.findFirst(hql.toString());
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<HRZZUser> getUsers(String pid, String deptcode, String[] ids) throws Exception {
        return dao.find("from HRZZUser where pid='" + pid + "' and deptcode='" + deptcode + "' and id in(:idlist)", ids);
    }

    @Override
    public ProjectInfo getpProject(String id) throws Exception {
        return (ProjectInfo) dao.findUnique("from ProjectInfo where id='" + id + "'");
    }

    @Override
    public Map<String, Object> getPostUsers(int page, int perpage, HRZZUser user) throws Exception {
        StringBuffer queryHQL = new StringBuffer("select r.id,r.id as userid,r.name,r.gender,r.code,r.major,r.graduationtime,r.bx,r.guake,r.tc,u.point,r.deptname,r.gwname,r.op6,r.fqstate,r.fqfid ");
        StringBuffer countHQL = new StringBuffer("select count(1) ");

        StringBuffer condition = new StringBuffer();
        condition.append("from DoMS_User u,HR_ZZUser r where r.id=u.userid(+) ");

        condition.append(BeanTools.hqlWhere(user, "r"));
        condition.append(" order by u.point desc ");
        queryHQL.append(condition);
        countHQL.append(condition);

        return DbUtil.pageSqlQuery(page, perpage, queryHQL.toString(), countHQL.toString(), dao);
    }

    @SuppressWarnings("unchecked")
    @Override
    public Map<String, Object> getTjResumes(HRZZUser user) throws Exception {
        Map<String, Object> map = new HashMap<String, Object>();
        StringBuffer queryHQL = new StringBuffer("select r.id,r.name,r.gender,r.code,r.major from HRZZUser r where 1=1 ");
        queryHQL.append(BeanTools.hqlWhere(user, "r"));
        List<Object[]> list = dao.find(queryHQL.toString());
        map.put("list", list);
        return map;
    }

    @Override
    public Map<String, Object> getMSUsers(int page, int perpage, HRZZUser user, String fid, String msresult, int notice) throws Exception {
        StringBuffer queryHQL = new StringBuffer("select r.id,s.id as userid,r.name,r.gender,r.code,r.major,r.job,s.starttime,s.address,s.notice,s.point,s.result,r.fid,r.fqstate,r.op2,s.scoringrules,r.msstate,du.id duserid ");
        StringBuffer countHQL = new StringBuffer("select count(1) ");

        StringBuffer condition = new StringBuffer();
        condition.append("from (select * from DoMS_User u where u.fid = '" + fid + "' ");


        if (notice != -1) {
            condition.append(" and u.notice='" + notice + "' ");
        }

        condition.append(") s,HR_ZZUser r,Duserresume du where r.id=s.userid(+) and r.code=du.cardno(+) and r.pid=du.proid(+) ");

        if (StringTools.trimNotEmpty(msresult)) {
            condition.append(" and s.result='" + msresult + "' ");
        }
        condition.append(BeanTools.hqlWhere(user, "r"));
        queryHQL.append(condition);
        countHQL.append(condition);

        return DbUtil.pageSqlQuery(page, perpage, queryHQL.toString(), countHQL.toString(), dao);
        //return  DbUtil.pageSqlQueryMap(page,perpage,queryHQL.toString(),null,countHQL.toString(),dao);
    }

    @Override
    public Map<String, Object> getMSUsers(int page, int perpage, HRZZUser user, String fid, String msresult, int notice, String msarrange) throws Exception {
        StringBuffer queryHQL = new StringBuffer("select r.id,s.id as userid,r.name,r.gender,r.code,r.major,r.job,s.starttime,s.address,s.notice,s.point,s.result,r.fid,r.fqstate,r.op2,s.scoringrules,r.msstate ");
        StringBuffer countHQL = new StringBuffer("select count(1) ");

        StringBuffer condition = new StringBuffer();
        condition.append(" from (select * from DoMS_User u where u.fid = '" + fid + "' ");


        if (notice != -1) {
            condition.append(" and u.notice='" + notice + "' ");
        }

        condition.append(") s,HR_ZZUser r,Duserresume du where r.id=s.userid(+) and r.code=du.cardno(+) and r.pid=du.proid(+) ");

        if (StringTools.trimNotEmpty(msresult)) {
            condition.append(" and s.result='" + msresult + "' ");
        }
        condition.append(BeanTools.hqlWhere(user, "r"));
        if(StringTools.trimNotEmpty(msarrange)){
            if (msarrange.equalsIgnoreCase("未安排")){
                condition.append(" and address is null ");
            }else if(msarrange.equalsIgnoreCase("已安排")){
                condition.append(" and address is not null  ");
            }

        }

        queryHQL.append(condition);
        countHQL.append(condition);
        return DbUtil.pageSqlQuery(page, perpage, queryHQL.toString(), countHQL.toString(), dao);
    }

    @Override
    public Map<String, Object> getMSUsersMap(int page, int perpage, HRZZUser user, String fid, String msresult, int notice, String msarrange,String scorearea) throws Exception {
        StringBuffer queryHQL = new StringBuffer("select r.id,s.id as userid,r.name,r.gender,r.code,r.major,r.job,s.starttime,s.address,s.notice,s.point,s.result,r.fid,r.fqstate,r.op2,s.scoringrules,r.msstate,du.id drid ,r.jname, du.msarrange");
        StringBuffer countHQL = new StringBuffer("select count(1) ");

        StringBuffer condition = new StringBuffer();
        condition.append(" from (select * from DoMS_User u where u.fid = '" + fid + "' ");


        if (notice != -1) {
            condition.append(" and u.notice='" + notice + "' ");
        }

        condition.append(") s,HR_ZZUser r,Duserresume du where r.id=s.userid(+) and r.code=du.cardno(+) and r.pid=du.proid(+) ");

        if (StringTools.trimNotEmpty(msresult)) {
            condition.append(" and s.result='" + msresult + "' ");
        }
        condition.append(BeanTools.hqlWhere(user, "r"));
        if(StringTools.trimNotEmpty(msarrange)){
            if (msarrange.equalsIgnoreCase("未安排")){
                condition.append(" and address is null ");
            }else if(msarrange.equalsIgnoreCase("已安排")){
                condition.append(" and address is not null  ");
            }

        }

        //2019-11-6 新增成绩区间条件筛选
        if (StringTools.notEmpty(scorearea)) {
            if (scorearea.toString().equalsIgnoreCase("L60")){
                condition.append(" and to_number(nvl(s.point,0))  <60 ");
            }else if(scorearea.toString().equalsIgnoreCase("R60")){
                condition.append(" and to_number(nvl(s.point,0))  >=60 ");
            }

        }


        queryHQL.append(condition);
        countHQL.append(condition);
        return DbUtil.pageSqlQueryMap(page, perpage, queryHQL.toString(),null, countHQL.toString(), dao);
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<HRZZInterview> getHrzzInterviews(String pid, String code) throws Exception {
        return dao.find("from HRZZInterview where pid='" + pid + "' and code='" + code + "'");
    }

    @Override
    public int getMSUserCount(String pid, String deptcode, String msstate, int state) {
        try {
            String hql = "select count(*) from HRZZUser where pid='" + pid
                    + "' and deptcode='" + deptcode + "' and msstate='" + msstate + "' and state='" + state + "'";
            return ((Long) dao.findUnique(hql)).intValue();
        } catch (Exception e) {
            e.printStackTrace();
            return -1;
        }
    }

    @Override
    public UserResume getResume(String code) throws Exception {
        return (UserResume) dao.findUnique("from UserResume where cardno='" + code + "'");
    }

    @Override
    public int getZSUserCount(String pid, String deptcode, String zsstate, String msstate) {
        try {
            String hql = "select count(*) from HRZZUser where pid='" + pid
                    + "' and deptcode='" + deptcode + "' and zsstate='" + zsstate + "' and msstate='" + msstate + "'";
            return ((Long) dao.findUnique(hql)).intValue();
        } catch (Exception e) {
            e.printStackTrace();
            return -1;
        }
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<String> getTjTimeList(String pid, String deptcode) throws Exception {
        return dao.executeSQLQuery("select distinct(tjtime) from HR_ZZUser where pid='" + pid + "' and deptcode='" + deptcode + "'");
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<HRZZUser> getTjUsers(String pid, String deptcode, String tjtime) throws Exception {
        return dao.find("from HRZZUser where pid='" + pid + "' and deptcode='" + deptcode + "' and tjtime='" + tjtime + "'");
    }

    @Override
    public int getTJUserCount(String pid, String deptcode, String tjstate, String zsstate) {
        try {
            String hql = "select count(*) from HRZZUser where pid='" + pid
                    + "' and deptcode='" + deptcode + "' and tjstate='" + tjstate + "' and zsstate='" + zsstate + "'";
            return ((Long) dao.findUnique(hql)).intValue();
        } catch (Exception e) {
            e.printStackTrace();
            return -1;
        }
    }

    @Override
    public int getGWUserCount(String pid, String deptcode, String gwstate, String tjstate) {
        try {
            String hql = "select count(*) from HRZZUser where pid='" + pid
                    + "' and deptcode='" + deptcode + "' and gwstate='" + gwstate + "' and tjstate='" + tjstate + "'";
            return ((Long) dao.findUnique(hql)).intValue();
        } catch (Exception e) {
            e.printStackTrace();
            return -1;
        }
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<Object[]> getDemands(String pid) throws Exception {
        String hql = "select j.id,d.jobname from PLAN_PERSONDEMAND d,PROJECT_INFO_JOB j where d.id=j.did and j.pid='" + pid + "'";
        return dao.executeSQLQuery(hql);
    }

    @Override
    public DUserResume getResume(String code, String pid) throws Exception {
        return (DUserResume) dao.findUnique("from DUserResume where cardno='" + code + "' and proid='" + pid + "'");
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<String> getBdTimeList(String pid, String deptcode) throws Exception {
        return dao.executeSQLQuery("select distinct(bdtime) from HR_ZZUser where pid='" + pid + "' and deptcode='" + deptcode + "'");
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<HRZZUser> getBdUsers(String pid, String deptcode, String bdtime) throws Exception {
        return dao.find("from HRZZUser where pid='" + pid + "' and deptcode='" + deptcode + "' and bdtime='" + bdtime + "'");
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<Object[]> getBdUserCount(String pid) throws Exception {
        return dao.executeSQLQuery("select count(*),t.jid from DUSERRESUME t where t.proid='" + pid + "' and t.cardno in(select code from hr_zzuser where bdstate='已报到') group by t.jid");
    }

    @Override
    public ProjectInfo getProject(String id) throws Exception {
        return (ProjectInfo) dao.findUnique("from ProjectInfo where id='" + id + "'");
    }
}
